import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from ttkbootstrap.dialogs.dialogs import Messagebox

from tkinter import filedialog
import pandas as pd

from dialog import *
from untils import center_window, export_excel
from model import *

# 班级与学生综合管理界面
class ManagmentFrame(ttk.Frame):

    def __init__(self, master: ttk.Window):
        super().__init__(master, padding=10)
        self.pack(fill=BOTH, expand=YES)

        # 班级管理区域
        self.class_frame = ttk.Frame(master=self, height=400)
        self.class_frame.pack(fill=X, pady=1, side=TOP)
        self._create_class_frame()

        # 班级对应的学生管理区域
        self.student_frame = ttk.Frame(master=self)
        self.student_frame.pack(fill=X, pady=1)
        self._create_student_frame()
    
    # 班级管理区域
    def _create_class_frame(self):
        # 班级管理按钮栏
        buttonbar = ttk.Frame(self.class_frame)
        buttonbar.pack(fill=X, pady=1, side=TOP, expand=YES)

        btn = ttk.Button(
            master=buttonbar,
            text="删除班级",
            command=self._del_class
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        btn = ttk.Button(
            master=buttonbar,
            text="修改班级",
            command=self._update_class
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        btn = ttk.Button(
            master=buttonbar,
            text="添加班级",
            command=self._add_class
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        # 班级列表显示区域
        self.clsview = ttk.Treeview(
            master=self.class_frame,
            bootstyle=INFO,
            columns=[0, 1, 2],
            show=HEADINGS
        )
        self.clsview.pack(fill=BOTH, expand=YES, pady=10)
        self.clsview.heading(0, text="ID", anchor=W)
        self.clsview.heading(1, text="班级", anchor=W)
        self.clsview.heading(2, text="人数", anchor=W)
        self._classlist()

        self.clsview.bind(sequence="<<TreeviewSelect>>", func=self._clsview_select)

    # 班级对应的学生管理区域
    def _create_student_frame(self):
        # 班级对应的学生管理按钮栏
        buttonbar = ttk.Frame(self.student_frame)
        buttonbar.pack(fill=X, pady=1, side=TOP, expand=YES)

        btn = ttk.Button(
            master=buttonbar,
            text="导出",
            command=self._export
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        btn = ttk.Button(
            master=buttonbar,
            text="导入",
            command=self._import
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        btn = ttk.Button(
            master=buttonbar,
            text="删除学生",
            command=self._del_student
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        btn = ttk.Button(
            master=buttonbar,
            text="修改学生",
            command=self._update_student
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        btn = ttk.Button(
            master=buttonbar,
            text="添加学生",
            command=self._add_student
        )
        btn.pack(side=RIGHT, ipadx=5, ipady=5, padx=(2, 0))

        # 学生列表显示区域
        self.stuview = ttk.Treeview(
            master=self,
            bootstyle=INFO,
            columns=[0, 1, 2, 3 , 4, 5],
            show=HEADINGS
        )
        self.stuview.pack(fill=BOTH, expand=YES, pady=10)
        self.stuview.heading(0, text="ID", anchor=W)
        self.stuview.heading(1, text="学号", anchor=W)
        self.stuview.heading(2, text="姓名", anchor=W)
        self.stuview.heading(3, text="出生日期", anchor=W)
        self.stuview.heading(4, text="性别", anchor=W)
        self.stuview.heading(5, text="联系方式", anchor=W)
        self.stuview.column(
            column=0,
            width=60
        )
        self.stuview.column(
            column=1,
            width=120
        )
        self.stuview.column(
            column=2,
            width=130
        )
        self.stuview.column(
            column=3,
            width=80
        )
        self.stuview.column(
            column=4,
            width=60
        )

    def _clsview_select(self, *args):
        """ 班级列表选中时获取班级id """
        selected_item = self.clsview.selection()
        item = selected_item[0]
        values = self.clsview.item(item, 'values')
        self.clsid = int(values[0])

        self._student_list()

    def _add_class(self):
        """ 添加班级 """
        dialog = ClassDialog(self, size=[300, 130], callback=self._classlist)
        dialog.title("添加班级")
        dialog.resizable(FALSE, FALSE)
        center_window(dialog)

    def _classlist(self):
        """ 班级列表 """
        items = self.clsview.get_children()
        for item in items:
            self.clsview.delete(item)

        classlist = Clazz().select(Clazz.id, Clazz.name, Clazz.number).order_by(Clazz.id.desc()).tuples()
        for cls in classlist:
            self.clsview.insert(
                parent="",
                index=END,
                values=cls
            )

    def _del_class(self):
        """ 删除班级 """
        selected_item = self.clsview.selection()
        if selected_item:
            result = Messagebox.okcancel("是否要删除该班级？此操作无法恢复", title="提示")
            if "确定" == result:
                item = selected_item[0]
                values = self.clsview.item(item, 'values')
                if int(values[2]) > 0:
                    Messagebox.show_error("该班级下有学生，不能删除", title="提示")
                else:
                    qry = Clazz.delete().where(Clazz.id == int(values[0]))
                    qry.execute()
                    Messagebox.ok("删除班级成功", title="消息")
                    self._classlist()
        else:
            Messagebox.show_error("请选择要删除的班级", title="提示")

    def _update_class(self):
        """ 修改班级 """
        selected_item = self.clsview.selection()
        if selected_item:
            item = selected_item[0]
            values = self.clsview.item(item, 'values')
            cls = Clazz(id=int(values[0]), name=values[1])
            dialog = ClassDialog(self, size=[300, 130], cls=cls, type=Opr.UPDATE, callback=self._classlist)
            dialog.title("修改班级")
            dialog.resizable(FALSE, FALSE)
            center_window(dialog)
        else:
            Messagebox.show_error("请选择要修改的班级", title="提示")

    def _add_student(self):
        """ 添加学生 """
        selected_item = self.clsview.selection()
        if selected_item:
            item = selected_item[0]
            values = self.clsview.item(item, 'values')
            dialog = StudentDialog(self, size=[300, 350],
                                   cls=Clazz(id=int(values[0]), name=values[1]), callback=self._add_student_success)
            dialog.title("添加学生")
            dialog.resizable(FALSE, FALSE)
            center_window(dialog)
        else:
            Messagebox.show_error("请先选择要添加学生的班级", title="提示")

    def _add_student_success(self):
        """ 添加学生成功回调 """
        self._classlist()
        self._student_list()

    def _update_student(self):
        """ 修改学生 """
        selected_item = self.stuview.selection()
        if selected_item:
            item = selected_item[0]
            values = self.stuview.item(item, 'values')
            stu = Student.get_by_id(int(values[0]))
            dialog = StudentDialog(self, size=[300, 350], type=Opr.UPDATE, stu=stu, callback=self._student_list)
            dialog.title("修改学生")
            dialog.resizable(FALSE, FALSE)
            center_window(dialog)
        else:
            Messagebox.show_error("请选择要修改的学生", title="提示")

    def _del_student(self):
        """ 删除学生 """
        selected_item = self.stuview.selection()
        if selected_item:
            result = Messagebox.okcancel("是否要删除该学生？此操作无法恢复", title="提示")
            if "确定" == result:
                item = selected_item[0]
                values = self.stuview.item(item, 'values')
                # 删除学生
                stu: Student = Student.get_by_id(int(values[0]))
                qry = Student.delete().where(Student.id == int(values[0]))
                qry.execute()
                # 同步修改该班级下学生人数
                count = Student.select().where(Student.clazzid == stu.clazzid).count()
                qry = Clazz.update({Clazz.number: count}).where(Clazz.id==stu.clazzid)
                qry.execute()

                Messagebox.ok("删除学生成功", title="消息")
                self._classlist()
                self._student_list()
        else:
            Messagebox.show_error("请选择要删除的学生", title="提示")

    def _student_list(self):
        """ 学生列表 """
        items = self.stuview.get_children()
        for item in items:
            self.stuview.delete(item)

        stulist = Student().select(
            Student.id,
            Student.stuno,
            Student.name,
            Student.birth,
            Student.gender,
            Student.phone
            ).where(
                Student.clazzid == self.clsid
            ).order_by(Student.id.desc()).tuples()
        for stu in stulist:
            self.stuview.insert(
                parent="",
                index=END,
                values=stu
            )

    def _import(self):
        """ 导入学生数据 """
        selected_item = self.clsview.selection()
        if selected_item:
            item = selected_item[0]
            values = self.clsview.item(item, 'values')
            clazzid = int(values[0])
            clazzname = values[1]

            # 弹出文件选择对话框，指定文件类型过滤为Excel
            filetypes = (
                ("Excel 文件", "*.xlsx"),
                ("所有文件", "*.*")
            )
            file_path = filedialog.askopenfilename(filetypes=filetypes)
            if file_path:
                df = pd.read_excel(file_path, usecols=["学号", "姓名", "出生日期", "性别", "联系方式"])
                data = []
                for row in df.itertuples():
                    data.append(Student(
                        stuno=row.学号,
                        name=row.姓名,
                        birth=str(row.出生日期).split(" ")[0],
                        gender=row.性别,
                        phone=row.联系方式,
                        clazzid=clazzid,
                        clazzname=clazzname
                    ))
                # 批量插入学生数据
                Student.bulk_create(data)
                # 同步修改该班级下学生人数
                count = Student.select().where(Student.clazzid == clazzid).count()
                qry = Clazz.update({Clazz.number: count}).where(Clazz.id==clazzid)
                qry.execute()

                Messagebox.ok("导入学生完成", title="消息")
                self._classlist()
                self._student_list()
        else:
            Messagebox.show_error("请选择要导入学生的班级", title="提示")

    def _export(self):
        """ 导出指定班级学生数据 """
        selected_item = self.clsview.selection()
        if selected_item:
            item = selected_item[0]
            values = self.clsview.item(item, 'values')
            if int(values[2]) > 0:
                clazzname = values[1]
                # 弹出保存文件对话框，并设置默认文件名
                file_path = filedialog.asksaveasfilename(defaultextension=".xlsx",
                                                         filetypes=[("Excel 文件", "*.xlsx"), ("所有文件", "*.*")],
                                                         initialfile=f"{clazzname}学生数据.xlsx")
                if file_path:
                    headers = ["ID", "学号", "姓名", "出生日期", "性别", "联系方式"]
                    data = []
                    # 遍历学生数据，放入到 data 中
                    for item in self.stuview.get_children():
                        data.append(self.stuview.item(item, "values"))
                    # 导出 Excel
                    export_excel(headers, data , file_path)
            else:
                Messagebox.show_error("选择的班级下没有学生", title="提示")
        else:
            Messagebox.show_error("请选择要导出学生的班级", title="提示")